--    Author    : MaYasha
--    Name      : EDW.DS_LOAB_SUM.HQL
--    Functions : 
--    Purpose   : Daily saving the variation data from detail
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-23  Mayasha           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE EDW.DS_LOAB_SUM PARTITION (DATA_DATE = '#V_DATA_DATE#' ,DATA_SRC_ORG = '#V_DATA_SRC_ORG#')
SELECT 
    FIN_ORG_NO                                      -- 金融机构编码
    ,CLIENT_TYPE                                    -- 客户类型
    ,CLIENT_NO                                      -- 借款人代码
    ,CLIENT_INDUSTRY                                -- 贷款主体行业类别
    ,CLIENT_DIST                                    -- 借款人所在地区
    ,ECONOMY_TYPE                                   -- 企业出资人经济成分
    ,ENTERPRISE_SCALE                               -- 企业规模
    ,BUSINESS_TYPE                                  -- 贷款产品类别
    ,LOAN_INDUSTRY                                  -- 贷款实际投向
    ,DRAWDOWN_DATE                                  -- 贷款发放日期
    ,MATURITY_DATE                                  -- 贷款到期日期
    ,EXT_MATURITY_DATE                              -- 展期到期日期
    ,CCY                                            -- 贷款币种
    ,SUM(CASE WHEN LOAB.DATA_DATE = '#V_DATA_DATE#' THEN NVL(ACTUAL_BAL,0) ELSE 0 END) AS ACTUAL_BAL -- 贷款余额
    ,IF_FLOAT                                       -- 利率是否固定
    ,RATE                                           -- 利率水平
    ,GUARANTY_TYPE                                  -- 贷款担保方式
    ,FIVE_CLASS                                     -- 贷款质量
    ,LOAN_STATUS                                    -- 贷款状态
    ,TERM_CODE                                      -- 贷款期限
    ,SUM(CASE WHEN LOAB.DATA_DATE = '#V_DATA_DATE#' THEN NVL(WSUM_BAL,0) ELSE 0 END)     AS WSUM_BAL --余额加权值
    ,0 -- SUM(CASE WHEN LOAB.DATA_DATE = '#V_DATA_DATE#' THEN NVL(ACTUAL_BAL,0) ELSE 0 END)   AS BAL_LM   --余额上期末
    ,0 -- SUM(CASE WHEN LOAB.DATA_DATE = '#V_DATA_DATE#' THEN NVL(ACTUAL_BAL,0) ELSE 0 END)   AS BAL_YF   --余额年初值
    ,0 -- SUM(CASE WHEN LOAB.DATA_DATE = '#V_DATA_DATE#' THEN NVL(ACTUAL_BAL,0) ELSE 0 END)   AS BAL_LC   --余额去年同期
    ,0 -- SUM(CASE WHEN LOAB.DATA_DATE = '#V_DATA_DATE#' THEN NVL(WSUM_BAL,0) ELSE 0 END)     AS WBAL_LM  --余额加权值上期末
    ,0 -- SUM(CASE WHEN LOAB.DATA_DATE = '#V_DATA_DATE#' THEN NVL(WSUM_BAL,0) ELSE 0 END)     AS WBAL_YF  --余额加权值年初
    ,0 -- SUM(CASE WHEN LOAB.DATA_DATE = '#V_DATA_DATE#' THEN NVL(WSUM_BAL,0) ELSE 0 END)     AS WBAL_LC  --余额加权值去年同期
    ,ORG_TYPE                                       -- 机构类型
    ,TERM_TYPE                                      -- 期限类型
FROM EDW.AG_LOAB_INFO LOAB
WHERE (LOAB.DATA_DATE = '#V_DATA_DATE#'     --本期
       OR LOAB.DATA_DATE = '#V_DATA_DATE#'  --上期
       OR LOAB.DATA_DATE = '#V_DATA_DATE#'  --年初
       OR LOAB.DATA_DATE = '#V_DATA_DATE#'  --去年同期
      )
AND LOAB.DATA_SRC_ORG = '#V_DATA_SRC_ORG#'
GROUP BY FIN_ORG_NO        
         ,CLIENT_TYPE      
         ,CLIENT_NO        
         ,CLIENT_INDUSTRY  
         ,CLIENT_DIST      
         ,ECONOMY_TYPE     
         ,ENTERPRISE_SCALE 
         ,BUSINESS_TYPE    
         ,LOAN_INDUSTRY    
         ,DRAWDOWN_DATE    
         ,MATURITY_DATE    
         ,EXT_MATURITY_DATE
         ,CCY
         ,IF_FLOAT              
         ,RATE         
         ,GUARANTY_TYPE
         ,FIVE_CLASS   
         ,LOAN_STATUS  
         ,TERM_CODE
         ,ORG_TYPE          
         ,TERM_TYPE